>Chris Bartlett <c.bartlett@paradise.net.nz> writes:
>> I'm trying to set [now(), 2049-12-31 00:00:00) as the default for a
>> tsrange column (Postgres 9.2), but can't figure out how to do it. I'm
>> either getting syntax errors or now() is being evaluated, so that the
>> default becomes something like [2012-07-14 14:04:35, 2049-12-31
>> 00:00:00), which is not what I want. Can anyone point me in the right
>> direction, please?
>
>I think you'd need to use the constructor function, ie
>
> default tsrange(now(), '2049-12-31 00:00:00')
I had tried the constructor function and hadn't managed to get a
successful result. E.g.
alter table the_table alter column the_column set default
tsrange(now(), '2049-12-31 00:00:00');
-> ERROR: function tsrange(timestamp with time zone, unknown) does not exist
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
Realisation: now() is a timestamp with time zone, but my column is a
timestamp without time zone. So this works:
alter table the_table alter column the_column set default
tsrange(now()::timestamp without time zone, '2049-12-31
00:00:00'::timestamp without time zone);
-> ALTER TABLE
>BTW, that second value looks a whole lot like a poorly thought out
>substitute for 'infinity' ...
> regards, tom lane
That's certainly an interesting comment and I'm open to suggestions!
The original db has two columns (from_timestamp, to_timestamp). I
don't go for NULL in the to_timestamp column. Alternatively, a
timestamp very, very far in the future can throw off query planners.
Thanks,
Chris
